**********************************************************************************************
/* This file creates a table containing all project number for aid projects in th financial sector *
14.12.2018
Melvin H. L. Wong
**********************************************************************************************



** Melvin
global data "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\Data"
global dofiles "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\do-files"
global rawdata "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\Raw Data"
global outdir "D:\Users\wong\Dropbox\Geocoded Aid and Conflict\Data\Aid\list_financeaid_2018_12_14"
*/

cd "$outdir"

import delimited using "$data\Aid\projects_ancillary.csv", clear delimiter(",") //contains sector information
* Drop duplicates as these relate only to IEG Evaluations, which we do not consider here
*XXXXXX Melvin 29.12.2017: Checked the duplicates. Ok to use duplicates drop
duplicates drop projectid, force
save ancillary.dta, replace
** Import matches from AidData-GADM spatial join (Needs to be import excel as important information are lost, if delimited (.csv) is used.) 
import excel using "$data\Aid\alg.xls", firstrow clear
rename project_idC254 projectid
merge m:1 projectid using ancillary.dta, nogen keep(1 3) //no mismatch from master (melvin 29.12.2017)

keep mjsector* sector*pct projectid project_loC254 precision_N100 latitudeN1911 longitudeN1911 location_1C254 ISOC3 ID_0N100 ID_1N100 ID_2N100

rename projectid project_id
rename latitudeN1911 latitutde
rename longitudeN1911 longitude
rename ISOC3 ISO3

* note: do not drop project locations <=4 to evaluate the differences of precisely coded financial aid and "budgetary" financial aid
//drop if precision_N100<=4

* keep only WX projects
keep if mjsector1code=="WX" & sector1pct>=10 | mjsector2code=="WX" & sector2pct>=10 | mjsector3code=="WX" & sector3pct>=10 | mjsector4code=="WX" & sector4pct>=10 | mjsector5code=="WX" & sector5pct>=10

save WX_list_all.dta, replace

collapse precision_N100, by(project_id ISO3)
drop precision_N100
keep if ISO3=="AGO"| ISO3=="BDI"| ISO3=="BEN"| ISO3=="BFA"| ISO3=="BWA"| ISO3=="CAF"| ISO3=="CIV"| ISO3=="CMR"| ISO3=="COD"| ISO3=="COG"| ISO3=="DZA"| ISO3=="EGY"| ISO3=="ERI"| ISO3=="ETH"| ISO3=="GAB"| ISO3=="GHA"| ISO3=="GIN"| ISO3=="GMB"| ISO3=="GNB"| ISO3=="KEN"| ISO3=="LBR"| ISO3=="LBY"| ISO3=="LSO"| ISO3=="MAR"| ISO3=="MDG"| ISO3=="MLI"| ISO3=="MOZ"| ISO3=="MRT"| ISO3=="MUS"| ISO3=="MWI"| ISO3=="NAM"| ISO3=="NER"| ISO3=="NGA"| ISO3=="RWA"| ISO3=="SEN"| ISO3=="SLE"| ISO3=="SOM"| ISO3=="TCD"| ISO3=="TGO"| ISO3=="TUN"| ISO3=="TZA"| ISO3=="UGA"| ISO3=="ZAF"| ISO3=="ZMB"| ISO3=="ZWE"
sort project_id ISO3

save WX_list_projectnr.dta, replace
